﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

namespace AssetManagementSystem.Model.Dao.MSAccess {

    public class GroupDAO : AssetManagementSystem.Model.IDao.IGroupDAO {

        //The MSAccess database connection to use in all data access operations.
        private OleDbConnection dbConn;

        /// <summary>
        /// Constructs a new GroupDAO using a MSAccess database connection string.
        /// </summary>
        /// <param name="connectionString">MSAccess database connection string</param>
        public GroupDAO(string connectionString) {
            //create new database connection
            this.dbConn = new OleDbConnection(connectionString);
            //test the new database connection
            try {
                //make sure the connection can be opened.
                dbConn.Open();
            } catch (Exception e) {
                throw e;
            } finally {
                //close connection if goes went wrong.
                dbConn.Close();
            }
        }

        //====================================================================
        //  CRUD METHODS - CREATE, READ, UPDATE, DELETE
        //====================================================================

        /// <summary>
        /// Create a new group in the database, from an 'Group' business object.
        /// </summary>
        /// <param name="group">The group object to be stored in the database</param>
        public void CreateGroup(Group group) {
            //Make sure the group doesn't already exist
            if (GroupExists(group.Name)) {
                throw new Exception("A group with the name specified already exists.");
            } else {
                try {
                    //store the new group
                    dbConn.Open();
                    OleDbCommand oleDbCmd = new OleDbCommand(
                        "INSERT INTO [Group] ([GroupName], [Description]) " +
                        "VALUES(@GroupName, @Description)",
                        dbConn);
                    oleDbCmd.Parameters.AddWithValue("@GroupName", group.Name);
                    oleDbCmd.Parameters.AddWithValue("@Description", group.Description);
                    //make sure the database changed
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Group not stored. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }

                //now store each of the groups additional properties.
                foreach (string property in group.Properties) {
                    StoreGroupProperty(group.Name, property);
                }
            }
        }



        /// <summary>
        /// Retrieve a single group from the database, using its name as the PKey.
        /// </summary>
        /// <param name="name">The group's name</param>
        /// <returns>The 'Group' business object for the requested group.</returns>
        public Group GetGroup(string name) {
            Group group = null;
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM Group WHERE (GroupName = ?)", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("GroupName", name);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //if more or less than 1 group is returned for this id, throw an exception
                if (dataTable.Rows.Count != 1) {
                    throw new Exception(dataTable.Rows.Count.ToString() + " records found for group " + name + " expected 1");
                }
                //create an Group object for the record returned
                foreach (DataRow groupRow in dataTable.Rows) {
                    group = new Group(
                        groupRow["GroupName"].ToString(),
                        groupRow["Description"].ToString(),
                        new List<string>());
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }

            //Get the additional properties of the group.
            group.Properties = GetGroupProperties(group.Name);

            //return the group
            return group;
        }

        /// <summary>
        /// Returns a collection of all groups found within the database.
        /// </summary>
        /// <returns>A collection of all groups found within the database.</returns>
        public ICollection<Group> GetAllGroups() {
            ICollection<Group> allGroups = new List<Group>();
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM [Group]", dbConn);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //create an Group object for each record returned
                foreach (DataRow groupRow in dataTable.Rows) {
                    allGroups.Add(new Group(
                        groupRow["GroupName"].ToString(),
                        groupRow["Description"].ToString(),
                        new List<string>()));
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }

            //Get the additional properties of the groups.
            foreach (Group group in allGroups) {
                group.Properties = GetGroupProperties(group.Name);
            }

            //return the groups
            return allGroups;
        }

        /// <summary>
        /// Update the details of a group already stored within the database.
        /// </summary>
        /// <param name="group">The new details of the group to update. Name must remain the same as it is the PKey.</param>
        public void UpdateGroup(Group group) {
            //Ensure group already exists
            if (!GroupExists(group.Name)) {
                throw new Exception("Cannot update group, existing details not found in database.");
            } else {
                try {
                    OleDbCommand oleDbCmd = new OleDbCommand("UPDATE [Group] SET [Description] = ? WHERE [GroupName] = ?",
                        dbConn);
                    dbConn.Open();
                    oleDbCmd.Parameters.AddWithValue("Description", group.Description);
                    oleDbCmd.Parameters.AddWithValue("GroupName", group.Name);
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Group not updated. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }

            //now store each of the groups additional properties.
            foreach (string property in group.Properties) {
                StoreGroupProperty(group.Name, property);
            }
        }

        /// <summary>
        /// Delete data for a single group from the database.
        /// </summary>
        /// <param name="group">The group to be deleted.</param>
        public void DeleteGroup(Group group) {

            //start by deleting all the properties of the group
            foreach (string property in group.Properties) {
                DeleteGroupProperty(group.Name, property);
            }

            //Ensure group already exists
            if (!GroupExists(group.Name)) {
                throw new Exception("Cannot delete group, existing details not found in database.");
            } else {
                try {
                    //delete the group
                    OleDbCommand oleDbCmd = new OleDbCommand("DELETE * FROM [Group] WHERE [GroupName] = ?", dbConn);
                    oleDbCmd.Parameters.AddWithValue("GroupName", group.Name);
                    dbConn.Open();
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Group not deleted. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        //====================================================================
        //  END - CRUD METHODS
        //====================================================================

        //====================================================================
        //  UTILITY METHODS - USEFUL WHEN PERFORMING OTHER OPERATIONS
        //====================================================================

        /// <summary>
        /// Returns true if an group exists with the specified Group Id, otherwise returns false.
        /// </summary>
        /// <param name="id">The group id to check for</param>
        /// <returns>True if an group exists with the specified Id, otherwise false.</returns>
        public bool GroupExists(string name) {
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM [Group] WHERE [GroupName] = ?", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("GroupName", name);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //return true if at least one student was returned.
                return dataTable.Rows.Count > 0;
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
        }

        /// <summary>
        /// Returns true if a property with the specified name exists for the specified group
        /// </summary>
        /// <param name="groupId">The group whos property is being found</param>
        /// <param name="name">The name of the property to find</param>
        /// <returns>True if a property with the specified name exists for the specified group</returns>
        public bool PropertyExists(string groupName, string name) {
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM GroupProperty WHERE GroupName = ? AND PropertyName = ?", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("GroupName", groupName);
                dAdapter.SelectCommand.Parameters.AddWithValue("PropertyName", name);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //return true if at least one student was returned.
                return dataTable.Rows.Count > 0;
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
        }

        /// <summary>
        /// Stores a group property, creates a new property if one does not already exist, otherwise it updates the existing one.
        /// </summary>
        /// <param name="groupName">The name of the group this property is for</param>
        /// <param name="property">The property to create or update</param>
        private void StoreGroupProperty(string groupName, string property) {
            if (PropertyExists(groupName, property)) {
                //no need to perform any action
            } else {
                //create new property
                try {
                    //store the new group property
                    dbConn.Open();
                    OleDbCommand oleDbCmd = new OleDbCommand(
                        "INSERT INTO GroupProperty ([GroupName], [PropertyName]) " +
                        "VALUES(@GroupName, @PropertyName)",
                        dbConn);
                    oleDbCmd.Parameters.AddWithValue("@GroupName", groupName);
                    oleDbCmd.Parameters.AddWithValue("@PropertyName", property);
                    //make sure the database changed
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Group Property '" + property + "' not stored. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        /// <summary>
        /// Gets all the properties of a specific group.
        /// </summary>
        /// <param name="groupName">The group whos properties are to be returned</param>
        /// <returns>The group's properties</returns>
        private List<string> GetGroupProperties(string groupName) {
            List<string> properties = new List<string>();
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM GroupProperty WHERE (GroupName = ?)", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("GroupName", groupName);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //add each record to the list of properties
                foreach (DataRow groupRow in dataTable.Rows) {
                    properties.Add(
                        groupRow["PropertyName"].ToString()
                        );
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
            return properties;
        }

        /// <summary>
        /// Deletes a group's property
        /// </summary>
        /// <param name="groupName">The group name of the group whos property is to be deleted</param>
        /// <param name="name">The name of the property to be deleted</param>
        public void DeleteGroupProperty(string groupName, string name) {
            //Ensure group property already exists
            if (!PropertyExists(groupName, name)) {
                //throw new Exception("Cannot delete property, existing details not found in database.");
            } else {
                try {
                    OleDbCommand oleDbCmd = new OleDbCommand("DELETE * FROM GroupProperty WHERE GroupName = ? AND PropertyName = ?", dbConn);
                    oleDbCmd.Parameters.AddWithValue("GroupName", groupName);
                    oleDbCmd.Parameters.AddWithValue("PropertyName", name);
                    dbConn.Open();
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        //throw new Exception("Property not deleted. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        //====================================================================
        //  END - UTILITY METHODS
        //====================================================================

    }
}
